﻿using ECommon.Helper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;

namespace EOrm
{
    /// <summary>
    /// 单表基础sql语句构建
    /// </summary>
    public partial class EOrmMap
    {
        /// <summary>
        /// 表名集合
        /// </summary>
        private static List<EOrmName> _tblNames = new List<EOrmName>();

        /// <summary>
        /// 表列集合
        /// </summary>
        private static Dictionary<string, List<EOrmName>> _colNames = new Dictionary<string, List<EOrmName>>();

        /// <summary>
        /// 构建选择语句
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="orderByPartial">排序部分语句</param>
        /// <returns>选择语句</returns>
        public static string SelectOrderBy<T>(string orderByPartial)
        {
            return Select<T>(null, orderByPartial);
        }

        /// <summary>
        /// 构建选择语句
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="wherePartial">条件部分语句</param>
        /// <param name="orderByPartial">排序部分语句</param>
        /// <returns>选择语句</returns>
        /// <exception cref="Exception">没有列异常</exception>
        public static string Select<T>(string wherePartial = "", string orderByPartial = "")
        {
            List<EOrmName> colNames = GetNames<T>(out EOrmName tblName);
            if (colNames.Count <= 0)
            {
                throw new Exception("没有任何列，无法构建sql");
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT ");
            stringBuilder.Append(string.Join(", ", colNames.Select(a => a.Alias).ToArray()));
            stringBuilder.Append(" FROM ");
            stringBuilder.Append(tblName.Alias);
            if (string.IsNullOrEmpty(wherePartial))
            {
                wherePartial = CreateWhere(colNames);
            }
            if (!string.IsNullOrEmpty(wherePartial))
            {
                stringBuilder.Append(" WHERE ");
                stringBuilder.Append(FormatPartial(wherePartial, " WHERE "));
            }
            if (string.IsNullOrEmpty(orderByPartial))
            {
                orderByPartial = CreateOrderBy(colNames);
            }
            if (!string.IsNullOrEmpty(orderByPartial))
            {
                stringBuilder.Append(" ORDER BY ");
                stringBuilder.Append(FormatPartial(orderByPartial, " ORDER BY "));
            }
            stringBuilder.Append(";");
            return stringBuilder.ToString();
        }

        /// <summary>
        /// 构建全部选择语句
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="orderByPartial">排序部分语句</param>
        /// <returns>全部选择语句</returns>
        /// <exception cref="Exception">没有列异常</exception>
        public static string AllOrderBy<T>(string orderByPartial)
        {
            return Select<T>(null, orderByPartial);
        }

        /// <summary>
        /// 构建选择语句
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="wherePartial">条件部分语句</param>
        /// <param name="orderByPartial">排序部分语句</param>
        /// <returns>选择语句</returns>
        /// <exception cref="Exception">没有列异常</exception>
        public static string All<T>(string wherePartial = "", string orderByPartial = "")
        {
            List<EOrmName> colNames = GetNames<T>(out EOrmName tblName);
            if (colNames.Count <= 0)
            {
                throw new Exception("没有任何列，无法构建sql");
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT ");
            stringBuilder.Append(string.Join(", ", colNames.Select(a => a.Alias).ToArray()));
            stringBuilder.Append(" FROM ");
            stringBuilder.Append(tblName.Alias);
            if (!string.IsNullOrEmpty(wherePartial))
            {
                stringBuilder.Append(" WHERE ");
                stringBuilder.Append(FormatPartial(wherePartial, " WHERE "));
            }
            if (string.IsNullOrEmpty(orderByPartial))
            {
                orderByPartial = CreateOrderBy(colNames);
            }
            if (!string.IsNullOrEmpty(orderByPartial))
            {
                stringBuilder.Append(" ORDER BY ");
                stringBuilder.Append(FormatPartial(orderByPartial, " ORDER BY "));
            }
            stringBuilder.Append(";");
            return stringBuilder.ToString();
        }

        /// <summary>
        /// 构建计数语句
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="wherePartial">条件部分语句</param>
        /// <returns>计数语句</returns>
        /// <exception cref="Exception">没有列异常</exception>
        public static string Count<T>(string wherePartial = null)
        {
            List<EOrmName> colNames = GetNames<T>(out EOrmName tblName);
            if (colNames.Count <= 0)
            {
                throw new Exception("没有任何列，无法构建sql");
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT COUNT(1) FROM ");
            stringBuilder.Append(tblName.Alias);
            if (!string.IsNullOrEmpty(wherePartial))
            {
                stringBuilder.Append(" WHERE ");
                stringBuilder.Append(FormatPartial(wherePartial, " WHERE "));
            }
            stringBuilder.Append(";");
            return stringBuilder.ToString();
        }


        /// <summary>
        /// 构建插入语句
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <returns>插入语句</returns>
        /// <exception cref="Exception">没有列异常</exception>
        public static string Insert<T>()
        {
            List<EOrmName> colNames = GetNames<T>(out EOrmName tblName);
            if (colNames.Count <= 0)
            {
                throw new Exception("没有任何列，无法构建sql");
            }
            List<EOrmName> editNames = FindEditNames(colNames);
            if (editNames.Count <= 0)
            {
                throw new Exception("没有任何可编辑列，无法构建sql");
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("INSERT INTO ");
            stringBuilder.Append(tblName.Alias);
            stringBuilder.Append(" (");
            stringBuilder.Append(string.Join(" ,", editNames.Select(a => a.Alias).ToArray()));
            stringBuilder.Append(") VALUES (");
            stringBuilder.Append(string.Join(" ,", editNames.Select(a => "@" + a.Name).ToArray()));
            stringBuilder.Append(");");
            return stringBuilder.ToString();
        }

        /// <summary>
        /// 构建更新语句
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="wherePartial">条件部分语句</param>
        /// <returns>更新语句</returns>
        /// <exception cref="Exception">没有列异常</exception>
        public static string Update<T>(string wherePartial = "")
        {
            List<EOrmName> colNames = GetNames<T>(out EOrmName tblName);
            if (colNames.Count <= 0)
            {
                throw new Exception("没有任何列，无法构建sql");
            }
            List<EOrmName> editNames = FindEditNames(colNames);
            if (editNames.Count <= 0)
            {
                throw new Exception("没有任何可编辑列，无法构建sql");
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("UPDATE ");
            stringBuilder.Append(tblName.Alias);
            stringBuilder.Append(" SET ");
            stringBuilder.Append(string.Join(" ,", editNames.Select(a => a.Alias + " = @" + a.Name).ToArray()));
            if (string.IsNullOrEmpty(wherePartial))
            {
                wherePartial = CreateWhere(colNames);
            }
            if (!string.IsNullOrEmpty(wherePartial))
            {
                stringBuilder.Append(" WHERE ");
                stringBuilder.Append(FormatPartial(wherePartial, " WHERE "));
            }
            stringBuilder.Append(";");
            return stringBuilder.ToString();
        }

        /// <summary>
        /// 构建删除语句
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="wherePartial">条件部分语句</param>
        /// <param name="isNameChanged">是否执行驼峰向下划线分割的转换</param>
        /// <returns>删除语句</returns>
        /// <exception cref="Exception">没有列异常</exception>
        public static string Delete<T>(string wherePartial = "")
        {
            List<EOrmName> colNames = GetNames<T>(out EOrmName tblName);
            if (colNames.Count <= 0)
            {
                throw new Exception("没有任何列，无法构建sql");
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("DELETE FROM ");
            stringBuilder.Append(tblName.Alias);
            if (string.IsNullOrEmpty(wherePartial))
            {
                wherePartial = CreateWhere(colNames);
            }
            if (!string.IsNullOrEmpty(wherePartial))
            {
                stringBuilder.Append(" WHERE ");
                stringBuilder.Append(FormatPartial(wherePartial, " WHERE "));
            }
            stringBuilder.Append(";");
            return stringBuilder.ToString();
        }

        /// <summary>
        /// 根据键值获取sql语句
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="key">键值</param>
        /// <returns>sql语句</returns>
        /// <exception cref="Exception">sql语句未配置异常</exception>
        public static string GetSql<T>(string key)
        {
            string tableName = typeof(T).Name;
            string tmpKey = tableName + "." + key;
            return GetSql(tmpKey);
        }

        /// <summary>
        /// 获取sql语句
        /// </summary>
        /// <param name="key">键值（形式：[文件名|表名].键值，其中'[文件名|表名].'为可选项）</param>
        /// <returns>sql语句</returns>
        /// <exception cref="Exception">键值配置异常</exception>
        public static string GetSql(string key)
        {
            if (string.IsNullOrEmpty(key))
            {
                throw new Exception("sql键值不可为空");
            }
            List<string> keyStrs = key.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries).ToList();
            if (keyStrs == null && keyStrs.Count == 0)
            {
                throw new Exception("sql键值形式错误");
            }
            if (keyStrs.Count == 1)
            {
                keyStrs.Insert(0, "Base");
            }
            string secondKey = keyStrs[1];
            string firstKey = keyStrs[0];
            string cfgName = "sql" + firstKey + ".xml";
            string sqlPath = SqlDir + cfgName;
            List<EOrmSql> tmp = XmlHelper.XmlDeserializeList<EOrmSql>(sqlPath);
            if (tmp == null)
            {
                tmp = new List<EOrmSql>();
            }
            EOrmSql sql = tmp.Find(a => a.Key == secondKey);
            if (sql == null)
            {
                sql = new EOrmSql() { Key = secondKey, Sql = "" };
                tmp.Add(sql);
                XmlHelper.XmlSerialize(tmp, sqlPath);
            }
            if (string.IsNullOrEmpty(sql.Sql))
            {
                throw new Exception(string.Format("请配置程序运行目录下Config\\SqlConfig子文件夹下{0}文件对应键值为{1}的Sql配置节内容", cfgName, secondKey));
            }
            return sql.Sql;

        }

        /// <summary>
        /// 构建排序语句
        /// </summary>
        /// <param name="colNames">列名</param>
        /// <returns>排序语句</returns>
        private static string CreateOrderBy(List<EOrmName> colNames)
        {
            string res = "";
            List<EOrmName> colNameInWhere = FindKeyNames(colNames);
            if (colNameInWhere.Count > 0)
            {
                res = string.Join(", ", colNameInWhere.Select(a => a.Alias + " " + EOrmBase.OrderMethod.ToString()).ToArray());
            }
            return res;
        }

        /// <summary>
        /// 获取键名
        /// </summary>
        /// <param name="colNames">列名</param>
        /// <returns>键名</returns>

        private static List<EOrmName> FindKeyNames(List<EOrmName> colNames)
        {
            List<EOrmName> res = new List<EOrmName>();
            EOrmName name = colNames.Find(a => a.Alias.ToLower() == "id");
            if (name != null)
            {
                res.Add(name);
                return res;
            }
            res = colNames.FindAll(a => a.Alias.ToLower().EndsWith("id")).ToList();
            if (res == null || res.Count <= 0)
            {
                res = colNames.FindAll(a => a.Alias.ToLower().EndsWith("no")).ToList();
            }
            return res;
        }

        /// <summary>
        /// 获取可编辑列名
        /// </summary>
        /// <param name="colNames">列名</param>
        /// <returns>可编辑列名</returns>

        private static List<EOrmName> FindEditNames(List<EOrmName> colNames)
        {
            return colNames.FindAll(a => a.Alias.ToLower() != "id").ToList();
        }

        /// <summary>
        /// 构建条件语句
        /// </summary>
        /// <param name="colNames">列特征</param>
        /// <returns>条件语句</returns>
        private static string CreateWhere(List<EOrmName> colNames)
        {
            string res = "";
            List<EOrmName> colNameInWhere = FindKeyNames(colNames);
            if (colNameInWhere.Count > 0)
            {
                res = string.Join(" " + EOrmBase.WhereLogic.ToString() + " ", colNameInWhere.Select(a => string.Format("{0} = @{1}", a.Alias, a.Name)).ToArray());
            }
            return res;
        }

        /// <summary>
        /// 格式化部分语句
        /// </summary>
        /// <param name="partial">部分语句</param>
        /// <param name="fmtStr">要格式化的部分</param>
        /// <returns>格式化后的语句</returns>
        private static string FormatPartial(string partial, string fmtStr)
        {
            string usefullPartial = partial.ToLower();
            if (usefullPartial.StartsWith(" " + fmtStr.ToLower()))
            {
                usefullPartial = partial.Substring(fmtStr.Length + 1);
            }
            else if (usefullPartial.StartsWith(fmtStr.ToLower()))
            {
                usefullPartial = partial.Substring(fmtStr.Length);
            }
            else
            {
                usefullPartial = partial;
            }
            return usefullPartial;
        }

        /// <summary>
        /// 获取实体表名和列名
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="tblName">表名</param>
        /// <returns>列名</returns>
        /// <exception cref="Exception">解析错误异常</exception>

        private static List<EOrmName> GetNames<T>(out EOrmName tblName)
        {
            DestructEntity<T>();
            string name = typeof(T).Name;
            EOrmName nameTmp = _tblNames.Find(a => a.Name == name);
            if (nameTmp == null)
            {
                throw new Exception("解析过程出错");
            }
            tblName = nameTmp;
            List<EOrmName> res = _colNames[name];
            if (res == null || res.Count <= 0)
            {
                throw new Exception("解析过程出错");
            }
            return res;
        }

        /// <summary>
        /// 解析实体
        /// </summary>
        /// <typeparam name="T">表对象</typeparam>
        private static void DestructEntity<T>()
        {
            Type type = typeof(T);
            string tblName = type.Name;
            if (_tblNames.Exists(a => a.Name == tblName))
            {
                return;
            }
            EOrmName ormTblName = new EOrmName(tblName);
            _tblNames.Add(ormTblName);
            List<EOrmName> colNames = null;
            if (_colNames.ContainsKey(tblName))
            {
                colNames = _colNames[tblName];
            }
            bool needAdd = colNames == null;
            if (needAdd)
            {
                colNames = new List<EOrmName>();
            }
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);
            pis.ToList().ForEach(pi =>
            {
                if (!colNames.Exists(a => a.Name == pi.Name))
                {
                    EOrmName newName = new EOrmName(pi.Name);
                    colNames.Add(newName);
                }
            });
            if (needAdd)
            {
                _colNames[tblName] = colNames;
            }
        }
        /// <summary>
        /// 默认sql存储目录
        /// </summary>
        internal static string SqlDir = AppDomain.CurrentDomain.BaseDirectory + "Config\\SqlConfig\\";
    }
}
